package com.apobates.jforum.grief.schema2doc.reactive.dialect;

import com.apobates.jforum.grief.schema2doc.core.SchemaTableStrategy;
import com.apobates.jforum.grief.schema2doc.core.entity.Column;
import com.apobates.jforum.grief.schema2doc.core.entity.Nullable;
import com.apobates.jforum.grief.schema2doc.core.entity.PrimayKey;
import com.apobates.jforum.grief.schema2doc.core.entity.Table;
import com.apobates.jforum.grief.schema2doc.core.schema.InformixHelper;
import com.apobates.jforum.grief.schema2doc.core.schema.SchemaDialect;
import com.apobates.jforum.grief.schema2doc.reactive.schema.AbstractRxJavaQueryExecutor;
import io.reactivex.Flowable;
import io.reactivex.Single;
import org.davidmoten.rx.jdbc.Database;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.Optional;

/**
 * RxJava2的Informix的结果集供应商
 * https://github.com/davidmoten/rxjava-jdbc
 */
public class RxJavaInformixExecutor extends AbstractRxJavaQueryExecutor {
    private final static Logger logger = LoggerFactory.getLogger(RxJavaInformixExecutor.class);

    protected RxJavaInformixExecutor(Database db, SchemaDialect dialect) {
        super(db, dialect);
    }

    @Override
    protected Flowable<Table> getTables(Database db, Optional<String> dbName, Optional<String> schema, SchemaTableStrategy tableStrategy) throws IllegalArgumentException {
        // https://www.ibm.com/docs/en/informix-servers/14.10?topic=functions-instr-function
        String sql="SELECT tabname, tabid FROM systables WHERE tabid >99";
        if(tableStrategy.sql(SchemaDialect.Informix).isPresent()){
            sql+= " AND " + tableStrategy.sql(SchemaDialect.Informix).get().replaceAll(SchemaTableStrategy.placeholder, "tabname");
        }
        logger.debug("[RxJava-Informix-TABLE]execute sql statement::"+sql);
        return db.select(sql).get(record -> {
            // String dbName, String tableName, String remarks
            return Table.noSchema(
                    record.getInt("tabid"),
                    dbName.get(),
                    record.getString("tabname"),
                    "description");
        }).filter(table->tableStrategy.filter().test(table.getTableName()));
    }

    @Override
    protected Single<List<Column>> getColumns(Database db, Table table) {
        // https://www.ibm.com/docs/en/informix-servers/12.10?topic=tables-syscolumns
        // https://www.ibm.com/docs/en/informix-servers/12.10?topic=tables-systables
        // https://www.ibm.com/docs/en/informix-servers/12.10?topic=tables-sysdefaults
        // https://www.ibm.com/docs/en/informix-servers/12.10?topic=tables-syscoldepend
        final List<String> pks = getPrimayKey(db, table.getId());
        String sql="SELECT c.colname, c.tabid, c.colno, c.coltype, c.collength, sd.default, sd.type, sd.class, tmp.constrtype " +
                "FROM syscolumns c " +
                "LEFT JOIN sysdefaults sd ON (sd.tabid=c.tabid AND sd.colno=c.colno) " +
                "LEFT JOIN " +
                "(SELECT sc.constrtype, scd.tabid, scd.colno FROM sysconstraints sc RIGHT JOIN syscoldepend scd ON sc.constrid = scd.constrid WHERE scd.tabid=:TID) " +
                "tmp ON (tmp.tabid=c.tabid AND tmp.colno=c.colno) WHERE c.tabid=:SCID";
        logger.debug("[RxJava-Informix-COLUMN]execute sql statement::"+sql);
        return db.select(sql).parameter("TID", table.getId()).parameter("SCID", table.getId()).get(record -> {
            // Basic:: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
            String columnName = record.getString("colname");
            return Column.basic(
                            columnName,
                            InformixHelper.getDatatype().get(record.getInt("coltype")),
                            record.getString("collength"),
                            Optional.ofNullable(record.getString("constrtype")).map(val->val.equals("N")? Nullable.NO:Nullable.YES).orElse(Nullable.YES), // NOT NULL
                            "description")
                    .toFull(
                            "0",
                            pks.contains(columnName)? PrimayKey.YES:PrimayKey.NO,
                            record.getString("default")); // String decimalDigits, PrimayKey pk, String columnDef
        }).toList();
    }

    private List<String> getPrimayKey(Database db, int tableId){
        // https://www.ibm.com/docs/en/informix-servers/14.10?topic=tables-sysindexes
        // SELECT COLUMN_NAME  FROM  ESB_pub_client.INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_NAME= 'JB_CLML'
        String sql = "SELECT c.colno,c.colname,sc.idxname FROM syscolumns c " +
                "JOIN SYSCONSTRAINTS sc ON sc.tabid = c.tabid " +
                "JOIN (" +
                "SELECT idxname,part1 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part1 >0 UNION ALL " +
                "SELECT idxname,part2 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part2 >0 UNION ALL " +
                "SELECT idxname,part3 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part3 >0 UNION ALL " +
                "SELECT idxname,part4 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part4 >0 UNION ALL " +
                "SELECT idxname,part5 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part5 >0 UNION ALL " +
                "SELECT idxname,part6 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part6 >0 UNION ALL " +
                "SELECT idxname,part7 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part7 >0 UNION ALL " +
                "SELECT idxname,part8 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part8 >0 UNION ALL " +
                "SELECT idxname,part9 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part9 >0 UNION ALL " +
                "SELECT idxname,part10 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part10 >0 UNION ALL " +
                "SELECT idxname,part11 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part11 >0 UNION ALL " +
                "SELECT idxname,part12 AS colno FROM SYSINDEXES si WHERE tabid={TID} AND part12 >0) AS t ON t.colno = c.colno AND t.idxname = sc.idxname " +
                "WHERE c.tabid={TID} AND sc.constrtype = 'P'";
        return db.select(sql.replace("{TID}", tableId+"")).get(record -> {
            return record.getString("colname");
        }).toList().blockingGet();
    }
}
